import pandas as pd
from MyTT import *
import akshare as ak
import numpy as np
import os
from datetime import datetime
from sqlalchemy import create_engine, distinct, or_, and_
import sqlite3
import backtrader as bt
import pymssql
from urllib.parse import quote_plus as urlquote
from configparser import ConfigParser

# https://zhuanlan.zhihu.com/p/618916750 均值回归策略
# https://zhuanlan.zhihu.com/p/380361696 order.executed.value的含义是该订单的成本
# 比如，你花100元买入1股股票，成本为100元，后来120卖掉它，成本还是100元，所以成本是不变的


def get_data(code):
    df = ak.stock_zh_a_daily(symbol=code, adjust='qfq')
    df.index = pd.to_datetime(df.date)
    df.sort_index(inplace=True)

    return df


class MeanReversion(bt.Strategy):
    params = (('period', 20),
              ('devfactor', 1),
              ('stop_loss', 1.0),
              ('printlog', False),
              )

    def __init__(self):
        # 初始化交易指令、买卖价格和手续费
        self.order = None
        self.buyprice = None
        self.buycomm = None
        # 获取交易价格
        self.dataclose = self.datas[0].close
        # 计算价格均值
        self.mean = bt.indicators.SimpleMovingAverage(
            self.dataclose, period=self.params.period)
        # 计算标准差
        self.std = bt.indicators.StandardDeviation(
            self.dataclose, period=self.params.period)
        # 计算上下界
        self.upper = self.mean + self.params.devfactor * self.std
        self.lower = self.mean - self.params.devfactor * self.std
        # 计算买卖信号
        self.buy_signal = bt.indicators.CrossUp(self.dataclose, self.lower)
        self.sell_signal = bt.indicators.CrossDown(self.dataclose, self.upper)

    # 策略核心，根据条件执行买卖交易指令
    def next(self):
        if self.order:  # 检查是否有指令等待执行,
            return
        # 检查是否持仓
        if not self.position:  # 没有持仓
            # 执行买入条件判断：
            if self.buy_signal:
                self.log(f'执行买入,{self.dataclose[0]:.2f}')
                # 获取当前的账户价值
                cash = self.broker.getcash()
                # 一手为100股
                # lots = int((cash * 0.95 / 100) / self.datas[0].close[0]) * 100
                # 95%仓买入
                self.order = self.buy(size=100)
                # 设置跟踪止损10%,如需设置金额止损可用trailamount
                self.order = self.sell(size=100, exectype=bt.Order.StopTrail, trailpercent=self.p.stop_loss)
        else:
            # 执行卖出条件判断：收盘价格跌破15日均线时满仓卖出
            if self.sell_signal:
                self.log(f'执行卖出,{self.dataclose[0]:.2f}')
                # 执行卖出
                self.order = self.close()

    def notify_order(self, order):
        # 未被处理的订单
        if order.status in [order.Submitted, order.Accepted]:
            return
        # 已经处理的订单
        if order.status in [order.Completed, order.Canceled, order.Margin]:
            if order.isbuy():
                self.log(
                    f'code={order.data._name} BUY CREATE TIME: {bt.num2date(order.created.dt)}, EXECUTED TIME: {bt.num2date(order.executed.dt)}')
                self.log(
                    'BUY EXECUTED, ref:%.0f，Price: %.2f, Cost: %.2f, Comm %.2f, Size: %.2f, Stock: %s' %
                    (order.ref,  # 订单编号
                     order.executed.price,  # 成交价
                     order.executed.value,  # 成交额
                     order.executed.comm,  # 佣金
                     order.executed.size,  # 成交量
                     order.data._name))  # 股票名称
            else:  # Sell
                self.log(
                    f'code={order.data._name} SELL CREATE TIME: {bt.num2date(order.created.dt)}, EXECUTED TIME: {bt.num2date(order.executed.dt)}')
                self.log('SELL EXECUTED, ref:%.0f, Price: %.2f, Cost: %.2f, Comm %.2f, Size: %.2f, Stock: %s' %
                         (order.ref,
                          order.executed.price,
                          order.executed.value,
                          order.executed.comm,
                          order.executed.size,
                          order.data._name))

            self.bar_executed = len(self)

        # Write down: no pending order
        self.order = None

    def log(self, txt, dt=None, doprint=True):
        if self.params.printlog or doprint:
            dt = dt or self.datas[0].datetime.date(0)
            # print(f'{dt.isoformat()},{txt}')
            print(txt)


def bt_result(data, st_date, ed_date, strategy=None, startcash=100000, commission=0.0005, stop_loss=1.0, printlog=False, flag=False):
    # 初始化cerebro回测系统设置
    cerebro = bt.Cerebro()

    datafeed = bt.feeds.PandasData(dataname=data, fromdate=st_date, todate=ed_date)
    cerebro.adddata(datafeed, name=code)
    # 将交易策略加载到回测系统中
    cerebro.addstrategy(strategy, stop_loss=stop_loss)
    cerebro.broker.setcash(startcash)
    # 设置交易手续费为 0.05%
    cerebro.broker.setcommission(commission=0.0005)
    cerebro.addanalyzer(bt.analyzers.PyFolio, _name='pyfolio')
    results = cerebro.run()
    strat = results[0]
    pyfoliozer = strat.analyzers.getbyname('pyfolio')
    returns, positions, transactions, gross_lev = pyfoliozer.get_pf_items()

    # 获取回测结束后的总资金
    portvalue = cerebro.broker.getvalue()
    pnl = portvalue - startcash
    # 打印结果
    print(f'总资金: {round(portvalue, 2)}')
    print(f'净收益: {round(pnl, 2)}')
    if flag:
        return returns, positions, transactions
    cerebro.plot()


code = 'sh600446'
st_date = datetime(2023, 1, 1)
ed_date = datetime(2023, 6, 16)
data = get_data(code)
bt_result(data, st_date, ed_date, MeanReversion)
# cerebro = bt.Cerebro()
# datafeed = bt.feeds.PandasData(dataname=data)
# cerebro.adddata(datafeed, name=code)
